CREATE DATABASE /*!32312 IF NOT EXISTS*/`tsblog` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `tsblog`;

/*Table structure for table `tb_post` */

DROP TABLE IF EXISTS `tb_post`;

CREATE TABLE `tb_post` (
  `Id` int(12) NOT NULL AUTO_INCREMENT,
  `Title` varchar(255) DEFAULT '' COMMENT '标题',
  `Content` text COMMENT '内容',
  `AuthorId` int(6) DEFAULT '0' COMMENT '作者ID',
  `AuthorName` varchar(50) DEFAULT '' COMMENT '作者姓名',
  `CreatedAt` datetime DEFAULT NULL COMMENT '创建时间',
  `PublishedAt` datetime DEFAULT NULL COMMENT '发布时间',
  `IsDeleted` bit(1) DEFAULT b'0' COMMENT '是否标识已删除[0:否,1:是],默认值:0',
  `AllowShow` bit(1) DEFAULT b'1' COMMENT '是否允许展示[0:否,1:是],默认值:1',
  `ViewCount` int(10) DEFAULT '0' COMMENT '浏览量',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Data for the table `tb_post` */

insert  into `tb_post`(`Id`,`Title`,`Content`,`AuthorId`,`AuthorName`,`CreatedAt`,`PublishedAt`,`IsDeleted`,`AllowShow`,`ViewCount`) values 
(1,'[MySQL]MySQL数据库如何按某列分组排序后查询每个分组的最后一条数据？','<p><a href=\"https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group\">https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group</a><br>[MySQL]MySQL数据库如何按某列分组排序后查询每个分组的最后一条数据？</p>\n<p>比如当前有如下的消息表(messages)及示例数据：</p>\n<pre><code>Id   Name   Other_Columns\n-------------------------\n1    A       A_data_1\n2    A       A_data_2\n3    A       A_data_3\n4    B       B_data_1\n5    B       B_data_2\n6    C       C_data_1\n</code></pre><p>按照以下SQL语句查询：</p>\n<pre><code>select * from messages group by name\n</code></pre><p>得到的查询结果为：</p>\n<pre><code>1    A       A_data_1\n4    B       B_data_1\n6    C       C_data_1\n</code></pre><p>如何使用SQL语句查询每个分组的最后一条数据，如：</p>\n<pre><code>3    A       A_data_3\n5    B       B_data_2\n6    C       C_data_1\n</code></pre><h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>SELECT m1.*\nFROM messages m1 LEFT JOIN messages m2\n ON (m1.name = m2.name AND m1.id &lt; m2.id)\nWHERE m2.id IS NULL;\n</code></pre><p>如果中MySQL 8.0中，可以使用 <em>WITH</em> 的语法(<a href=\"https://dev.mysql.com/doc/refman/8.0/en/with.html\"><em>WITH语法文档</em></a>)，具体实现SQL语句如下：</p>\n<pre><code>WITH ranked_messages AS (\n  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn\n  FROM messages AS m\n)\nSELECT * FROM ranked_messages WHERE rn = 1;\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>使用子查询</p>\n<pre><code>select\n    a.*\nfrom\n    messages a\n    inner join \n        (select name, max(id) as maxid from messages group by name) as b on\n        a.id = b.maxid\n</code></pre><p>如果不是以 <em>id</em> 列排序的，则指定其他列即可，如：</p>\n<pre><code>select\n    a.*\nfrom\n    messages a\n    inner join \n        (select name, max(other_col) as other_col \n         from messages group by name) as b on\n        a.name = b.name\n        and a.other_col = b.other_col\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>SELECT\n  Id, Name, OtherColumns\nFROM messages\nWHERE NOT EXISTS (\n  SELECT * FROM messages as M2\n  WHERE M2.Name = messages.Name\n  AND M2.Id &gt; messages.Id\n)\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>SELECT jos_categories.title AS name,\n       joined .catid,\n       joined .title,\n       joined .introtext\nFROM   jos_categories\n       INNER JOIN (SELECT *\n                   FROM   (SELECT `title`,\n                                  catid,\n                                  `created`,\n                                  introtext\n                           FROM   `jos_content`\n                           WHERE  `sectionid` = 6\n                           ORDER  BY `id` DESC) AS yes\n                   GROUP  BY `yes`.`catid` DESC\n                   ORDER  BY `yes`.`created` DESC) AS joined\n         ON( joined.catid = jos_categories.id )\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>SELECT \n  `Id`,\n  `Name`,\n  SUBSTRING_INDEX(\n    GROUP_CONCAT(\n      `Other_Columns` \n      ORDER BY `Id` DESC \n      SEPARATOR &#39;||&#39;\n    ),\n    &#39;||&#39;,\n    1\n  ) Other_Columns \nFROM\n  messages \nGROUP BY `Name`\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(2,'[MySQL]MySQL数据库中如何查询出所有包含指定的一个或者多个字段名(列名)的数据表集合？','<p>[MySQL]MySQL数据库中如何查询出所有包含指定的一个或者多个字段名(列名)的数据表集合？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如当前有几个字段(列)名的集合，需要查询某个MySQL数据库中所有包含的这些字段(列)集合的数据表，在MySQL语句中应该如何实现呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>SELECT DISTINCT TABLE_NAME \n    FROM INFORMATION_SCHEMA.COLUMNS\n    WHERE COLUMN_NAME IN (&#39;columnA&#39;,&#39;ColumnB&#39;)\n        AND TABLE_SCHEMA=&#39;YourDatabase&#39;;\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>SELECT TABLE_NAME, COLUMN_NAME\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE COLUMN_NAME LIKE &#39;%wild%&#39;;\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>SELECT * FROM information_schema.columns WHERE column_name = &#39;column_name&#39;;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>SELECT DISTINCT TABLE_NAME, COLUMN_NAME  \nFROM INFORMATION_SCHEMA.COLUMNS  \nWHERE column_name LIKE &#39;employee%&#39;  \nAND TABLE_SCHEMA=&#39;YourDatabase&#39;\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE \nTABLE_SCHEMA = &#39;your_db_name&#39; AND TABLE_NAME NOT IN (SELECT DISTINCT \nTABLE_NAME FROM information_schema.columns WHERE column_name = \n&#39;column_name&#39; AND TABLE_SCHEMA = &#39;your_db_name&#39;);\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>SELECT DISTINCT TABLE_NAME, COLUMN_NAME  \nFROM INFORMATION_SCHEMA.COLUMNS  \nWHERE column_name LIKE &#39;%&#39;  \nAND TABLE_SCHEMA=&#39;tresbu_lk&#39;\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(3,'[MySQL]MySQL数据库中如何为日期类型(Datetime)的字段(列)设置当前时间为默认值？','<p>[MySQL]MySQL数据库中如何为日期类型(Datetime)的字段(列)设置当前时间为默认值？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>如题，在MySQL数据库中，如何为日期类型(Datetime)的字段(列)设置当前时间为默认值？<br>我们知道，在SQL Server数据库中，可以使用 <code>GETDATE()</code> 函数来设置某个字段(列)的默认当前时间，那么，在MySQL数据库如何实现呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>在MySQL 5.6.5 版本的数据库中，可以使用 <em>CURRENT_TIMESTAMP</em> 来设置默认值为当前数据库服务器时间，具体请看示例：</p>\n<pre><code>mysql&gt; create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql&gt; desc test;\n+-------+-------------+------+-----+-------------------+-------+\n| Field | Type        | Null | Key | Default           | Extra |\n+-------+-------------+------+-----+-------------------+-------+\n| str   | varchar(32) | YES  |     | NULL              |       | \n| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | \n+-------+-------------+------+-----+-------------------+-------+\n2 rows in set (0.00 sec)\n\nmysql&gt; insert into test (str) values (&quot;demo&quot;);\nQuery OK, 1 row affected (0.00 sec)\n\nmysql&gt; select * from test;\n+------+---------------------+\n| str  | ts                  |\n+------+---------------------+\n| demo | 2008-10-03 22:59:52 | \n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql&gt;\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>甚至，你还可以为更新时的列设置当前更新时间，具体SQL语句如下：</p>\n<pre><code>CREATE TABLE foo (\n    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,\n    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP\n)\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><p>除了使用 <em>CURRENT_TIMESTAMP</em> 关键字外，我们还可以使用 <em>TRIGGER</em> 触发器来设置，具体SQL语句示例：</p>\n<pre><code>CREATE TABLE `MyTable` (\n`MyTable_ID`  int UNSIGNED NOT NULL AUTO_INCREMENT ,\n`MyData`  varchar(10) NOT NULL ,\n`CreationDate`  datetime NULL ,\n`UpdateDate`  datetime NULL ,\nPRIMARY KEY (`MyTable_ID`)\n)\n;\n\nCREATE TRIGGER `MyTable_INSERT` BEFORE INSERT ON `MyTable`\nFOR EACH ROW BEGIN\n        -- Set the creation date\n    SET new.CreationDate = now();\n\n        -- Set the udpate date\n    Set new.UpdateDate = now();\nEND;\n\nCREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`\nFOR EACH ROW BEGIN\n        -- Set the udpate date\n    Set new.UpdateDate = now();\nEND;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><p>当我们为已经存在的数据表字段(列)设置默认值为当前时间时，可以使用如下SQL语句:</p>\n<pre><code>ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT &#39;CURRENT_TIMESTAMP&#39;\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(4,'.NET[C#]使用LINQ从List<T>集合中获取最后N条数据记录的方法有哪些？','<p>.NET[C#]使用LINQ从List&lt;T&gt;集合中获取最后N条数据记录的方法有哪些？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>collection.Skip(Math.Max(0, collection.Count() - N));\n</code></pre><p>我们也可以把它写成一个静态扩展方法，如：</p>\n<pre><code>public static class MiscExtensions\n{\n    public static IEnumerable&lt;T&gt; TakeLast&lt;T&gt;(this IEnumerable&lt;T&gt; source, int N)\n    {\n        return source.Skip(Math.Max(0, source.Count() - N));\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>collection.TakeLast(5);\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>coll.Reverse().Take(N).Reverse().ToList();\n</code></pre><p>静态扩展类如：</p>\n<pre><code>public static IEnumerable&lt;T&gt; TakeLast&lt;T&gt;(this IEnumerable&lt;T&gt; coll, int N)\n{\n    return coll.Reverse().Take(N).Reverse();\n}\n</code></pre><p>调用方法：</p>\n<pre><code>coll.TakeLast(5);\n</code></pre><p>如果不想使用静态扩展方法，还可以使用 <em>Enumerable.Reverse()</em> 方法，如下：</p>\n<pre><code>List&lt;string&gt; mystring = new List&lt;string&gt;() { &quot;one&quot;, &quot;two&quot;, &quot;three&quot; }; \nmystring = Enumerable.Reverse(mystring).Take(2).Reverse().ToList();\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>public static class Extensions\n{\n    public static IEnumerable&lt;T&gt; TakeLast&lt;T&gt;(this IEnumerable&lt;T&gt; collection,\n        int n)\n    {\n        if (collection == null)\n            throw new ArgumentNullException(&quot;collection&quot;);\n        if (n &lt; 0)\n            throw new ArgumentOutOfRangeException(&quot;n&quot;, &quot;n must be 0 or greater&quot;);\n\n        LinkedList&lt;T&gt; temp = new LinkedList&lt;T&gt;();\n\n        foreach (var value in collection)\n        {\n            temp.AddLast(value);\n            if (temp.Count &gt; n)\n                temp.RemoveFirst();\n        }\n\n        return temp;\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>IEnumerable&lt;int&gt; sequence = Enumerable.Range(1, 10000);\nIEnumerable&lt;int&gt; last10 = sequence.TakeLast(10);\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>public static class TakeLastExtension\n{\n    public static IEnumerable&lt;T&gt; TakeLast&lt;T&gt;(this IEnumerable&lt;T&gt; source, int takeCount)\n    {\n        if (source == null) { throw new ArgumentNullException(&quot;source&quot;); }\n        if (takeCount &lt; 0) { throw new ArgumentOutOfRangeException(&quot;takeCount&quot;, &quot;must not be negative&quot;); }\n        if (takeCount == 0) { yield break; }\n\n        T[] result = new T[takeCount];\n        int i = 0;\n\n        int sourceCount = 0;\n        foreach (T element in source)\n        {\n            result[i] = element;\n            i = (i + 1) % takeCount;\n            sourceCount++;\n        }\n\n        if (sourceCount &lt; takeCount)\n        {\n            takeCount = sourceCount;\n            i = 0;\n        }\n\n        for (int j = 0; j &lt; takeCount; ++j)\n        {\n            yield return result[(i + j) % takeCount];\n        }\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>List&lt;int&gt; l = new List&lt;int&gt; {4, 6, 3, 6, 2, 5, 7};\nList&lt;int&gt; lastElements = l.TakeLast(3).ToList();\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>public static IEnumerable&lt;T&gt; FilterLastN&lt;T&gt;(this IEnumerable&lt;T&gt; source, int n, Predicate&lt;T&gt; pred)\n{\n    int goldenIndex = source.Count() - n;\n    return source.SkipWhile((val, index) =&gt; index &lt; goldenIndex &amp;&amp; pred(val));\n}\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>IEnumerable&lt;int&gt; source = Enumerable.Range(1, 10000);\n\nIEnumerable&lt;int&gt; lastThree = source.AsObservable().TakeLast(3).AsEnumerable();\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(5,'[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？','<p>[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>如题，[MySQL]MySQL数据库中如何使用SQL语句查看表或者列的所有外键？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>查看一张表的所有外键：</p>\n<pre><code>SELECT \n  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME\nFROM\n  INFORMATION_SCHEMA.KEY_COLUMN_USAGE\nWHERE\n  REFERENCED_TABLE_SCHEMA = &#39;&lt;database&gt;&#39; AND\n  REFERENCED_TABLE_NAME = &#39;&lt;table&gt;&#39;;\n</code></pre><p>查看一个列的所有外键：</p>\n<pre><code>SELECT \n  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME\nFROM\n  INFORMATION_SCHEMA.KEY_COLUMN_USAGE\nWHERE\n  REFERENCED_TABLE_SCHEMA = &#39;&lt;database&gt;&#39; AND\n  REFERENCED_COLUMN_NAME = &#39;&lt;column&gt;&#39;;\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>如果使用InnoDB引擎并且定义了外键：</p>\n<pre><code>SELECT * FROM information_schema.TABLE_CONSTRAINTS \nWHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = &#39;FOREIGN KEY&#39; \nAND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = &#39;myschema&#39;\nAND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = &#39;mytable&#39;;\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><p>查看一张表的所有外键：</p>\n<pre><code>USE &#39;&lt;yourschema&gt;&#39;;\n\nSELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME \nFROM information_schema.TABLE_CONSTRAINTS i \nLEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME \nWHERE i.CONSTRAINT_TYPE = &#39;FOREIGN KEY&#39; \nAND i.TABLE_SCHEMA = DATABASE()\nAND i.TABLE_NAME = &#39;&lt;yourtable&gt;&#39;;\n</code></pre><p>查看框架(schema)中所有表的所有外键：</p>\n<pre><code>USE &#39;&lt;yourschema&gt;&#39;;\n\nSELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME \nFROM information_schema.TABLE_CONSTRAINTS i \nLEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME \nWHERE i.CONSTRAINT_TYPE = &#39;FOREIGN KEY&#39; \nAND i.TABLE_SCHEMA = DATABASE();\n</code></pre><p>查看数据库中所有外键：</p>\n<pre><code>SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME \nFROM information_schema.TABLE_CONSTRAINTS i \nLEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME \nWHERE i.CONSTRAINT_TYPE = &#39;FOREIGN KEY&#39;;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><p>查看数据所有关系及约束等集合</p>\n<pre><code>select\n    concat(table_name, &#39;.&#39;, column_name) as &#39;foreign key&#39;,\n    concat(referenced_table_name, &#39;.&#39;, referenced_column_name) as &#39;references&#39;,\n    constraint_name as &#39;constraint name&#39;\nfrom\n    information_schema.key_column_usage\nwhere\n    referenced_table_name is not null;\n</code></pre><p>如果想要查看指定的数据库和数据表，则：</p>\n<pre><code>select\n    concat(table_name, &#39;.&#39;, column_name) as &#39;foreign key&#39;,\n    concat(referenced_table_name, &#39;.&#39;, referenced_column_name) as &#39;references&#39;,\n    constraint_name as &#39;constraint name&#39;\nfrom\n    information_schema.key_column_usage\nwhere\n    referenced_table_name is not null\n    and table_schema = &#39;database_name&#39;;\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(6,'.NET[C#]使用LINQ从List<T>集合中删除重复对象元素(去重)的方法有哪些？','<p>.NET[C#]使用LINQ从List&lt;T&gt;集合中删除重复对象元素(去重)的方法有哪些？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有如下的List集合：</p>\n<pre><code>1         Item1       IT00001        $100\n2         Item2       IT00002        $200\n3         Item3       IT00003        $150\n1         Item1       IT00001        $100\n3         Item3       IT00003        $150\n</code></pre><p>使用LINQ如何实现对以上List集合的去重操作，具体实现有哪些呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>var distinctItems = items.Distinct();\n</code></pre><p>如果需要对泛型实体中的部分属性进行去重操作，则可以创建一个自定义的比较器：</p>\n<pre><code>class DistinctItemComparer : IEqualityComparer&lt;Item&gt; {\n\n    public bool Equals(Item x, Item y) {\n        return x.Id == y.Id &amp;&amp;\n            x.Name == y.Name &amp;&amp;\n            x.Code == y.Code &amp;&amp;\n            x.Price == y.Price;\n    }\n\n    public int GetHashCode(Item obj) {\n        return obj.Id.GetHashCode() ^\n            obj.Name.GetHashCode() ^\n            obj.Code.GetHashCode() ^\n            obj.Price.GetHashCode();\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>var distinctItems = items.Distinct(new DistinctItemComparer());\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>var distinctItems = items.GroupBy(x =&gt; x.Id).Select(y =&gt; y.First());\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><p>使用 <a href=\"http://code.google.com/p/morelinq/wiki/OperatorsOverview\"><em>MoreLinq</em></a> 组件：</p>\n<pre><code>var distinct = items.DistinctBy( i =&gt; i.Id );\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>var query = collection.GroupBy(x =&gt; x.title).Select(y =&gt; y.FirstOrDefault());\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><p>创建静态扩展方法，如：</p>\n<pre><code>public static class DistinctHelper\n{\n    public static IEnumerable&lt;TSource&gt; DistinctBy&lt;TSource, TKey&gt;(this IEnumerable&lt;TSource&gt; source, Func&lt;TSource, TKey&gt; keySelector)\n    {\n        var identifiedKeys = new HashSet&lt;TKey&gt;();\n        return source.Where(element =&gt; identifiedKeys.Add(keySelector(element)));\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>var outputList = sourceList.DistinctBy(x =&gt; x.TargetProperty);\n</code></pre><p>其中 <code>x.TargetProperty</code> 请替换成类对应的属性。</p>\n<p>示例程序：</p>\n<pre><code>using System;\nusing System.Collections.Generic;\nusing System.Linq;\nusing System.Text;\nusing System.Threading.Tasks;\n\nnamespace ConsoleApplication1\n{\n    class Program\n    {\n        static void Main(string[] args)\n        {\n            var people = new List&lt;Person&gt;\n            {\n                new Person {Id=1,Name=&quot;Curry&quot;,Age=26 },\n                new Person {Id=1,Name=&quot;Curry&quot;,Age=26 },\n                new Person {Id=3,Name=&quot;James&quot;,Age=27 },\n                new Person {Id=4,Name=&quot;Kobe&quot;,Age=38 }\n            };\n            var distinctPeople = people.DistinctBy(x =&gt; x.Name).ToList();\n            distinctPeople.ForEach(x =&gt;\n            Console.WriteLine($&quot;Id:{x.Id},Name:{x.Name},Age:{x.Age}&quot;)\n            );\n            Console.ReadKey();\n        }\n    }\n\n    public class Person\n    {\n        public int Id { get; set; }\n        public string Name { get; set; }\n        public int Age { get; set; }\n    }\n\n    public static class DistinctHelper\n    {\n        public static IEnumerable&lt;TSource&gt; DistinctBy&lt;TSource, TKey&gt;(this IEnumerable&lt;TSource&gt; source, Func&lt;TSource, TKey&gt; keySelector)\n        {\n            var identifiedKeys = new HashSet&lt;TKey&gt;();\n            return source.Where(element =&gt; identifiedKeys.Add(keySelector(element)));\n        }\n    }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>public static class DistinctHelper\n    {\n        public static IEnumerable&lt;TSource&gt; DistinctBy&lt;TSource, TKey&gt;(this IEnumerable&lt;TSource&gt; source, Func&lt;TSource, TKey&gt; keySelector)\n        {\n            var identifiedKeys = new HashSet&lt;TKey&gt;();\n\n            foreach (var item in source)\n            {\n                if (identifiedKeys.Add(keySelector(item)))\n                    yield return item;\n            }\n        }\n    }\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(7,'.NET[C#]使用LINQ把两个数组集合连接成一个集合有哪些方式？','<p>.NET[C#]使用LINQ把两个数组集合连接成一个集合有哪些方式？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有如下的两个数组集合：</p>\n<pre><code>[1,2,3,4]\n[5,6,7]\n</code></pre><p>期望使用LINQ连接后的结果：</p>\n<pre><code>[1,2,3,4,5,6,7]\n</code></pre><p>使用LINQ把两个数组集合连接成一个数组集合有哪些方式？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>使用 <em>SelectMany()</em> 方法</p>\n<pre><code>var result = iList.SelectMany( i =&gt; i );\n</code></pre><p>或者</p>\n<pre><code>iList.SelectMany(x =&gt; x).ToArray()\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>使用 <em>QUERY</em> 语法</p>\n<pre><code>var values =\n            from inner in outer\n            from value in inner\n            select value;\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>int[] i1 = { 1, 2, 3, 4};\nint[] i2 = { 5, 6, 7};\nint[] i3 = i1.Concat(i2).ToArray();\n</code></pre><blockquote>\n<p>此方案由网友 <strong>CNXY</strong> 提供</p>\n</blockquote>\n',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(8,'.NET[C#]C#中如何使用反射调用泛型方法？','<p>.NET[C#]C#中如何使用反射调用泛型方法？</p>\n<h2 id=\"h2-u95EEu9898u6458u8981\"><a name=\"问题摘要\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题摘要</h2><p>比如有以下的包含泛型方法的类的代码片段：</p>\n<pre><code>public class Sample\n{\n    public void Example(string typeName)\n    {\n        Type myType = FindType(typeName);\n\n        // 此处如何使用反射调用GenericMethod&lt;T&gt;()?\n        GenericMethod&lt;myType&gt;(); // 这样做是不行的\n\n        // 此处如何使用反射调用StaticMethod&lt;T&gt;()?\n        Sample.StaticMethod&lt;myType&gt;(); // 这样做也是不行的\n    }\n\n    public void GenericMethod&lt;T&gt;()\n    {\n        // ...\n    }\n\n    public static void StaticMethod&lt;T&gt;()\n    {\n        //...\n    }\n}\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E00\"><a name=\"示例一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例一</h2><pre><code>MethodInfo method = typeof(Sample).GetMethod(&quot;GenericMethod&quot;);\nMethodInfo generic = method.MakeGenericMethod(myType);\ngeneric.Invoke(this, null);\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E8C\"><a name=\"示例二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例二</h2><pre><code>var name = InvokeMemberName.Create;\nDynamic.InvokeMemberAction(this, name(&quot;GenericMethod&quot;, new[]{myType}));\n\n\nvar staticContext = InvokeContext.CreateStatic;\nDynamic.InvokeMemberAction(staticContext(typeof(Sample)), name(&quot;StaticMethod&quot;, new[]{myType}));\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E09\"><a name=\"示例三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例三</h2><pre><code>using System;\nusing System.Collections;\nusing System.Collections.Generic;\n\nnamespace DictionaryRuntime\n{\n    public class DynamicDictionaryFactory\n    {\n        /// &lt;summary&gt;\n        /// Factory to create dynamically a generic Dictionary.\n        /// &lt;/summary&gt;\n        public IDictionary CreateDynamicGenericInstance(Type keyType, Type valueType)\n        {\n            //Creating the Dictionary.\n            Type typeDict = typeof(Dictionary&lt;,&gt;);\n\n            //Creating KeyValue Type for Dictionary.\n            Type[] typeArgs = { keyType, valueType };\n\n            //Passing the Type and create Dictionary Type.\n            Type genericType = typeDict.MakeGenericType(typeArgs);\n\n            //Creating Instance for Dictionary&lt;K,T&gt;.\n            IDictionary d = Activator.CreateInstance(genericType) as IDictionary;\n\n            return d;\n\n        }\n    }\n}\n</code></pre><p>调用程序：</p>\n<pre><code>using System;\nusing System.Collections.Generic;\n\nnamespace DynamicDictionary\n{\n    class Test\n    {\n        static void Main(string[] args)\n        {\n            var factory = new DictionaryRuntime.DynamicDictionaryFactory();\n            var dict = factory.CreateDynamicGenericInstance(typeof(String), typeof(int));\n\n            var typedDict = dict as Dictionary&lt;String, int&gt;;\n\n            if (typedDict != null)\n            {\n                Console.WriteLine(&quot;Dictionary&lt;String, int&gt;&quot;);\n\n                typedDict.Add(&quot;One&quot;, 1);\n                typedDict.Add(&quot;Two&quot;, 2);\n                typedDict.Add(&quot;Three&quot;, 3);\n\n                foreach(var kvp in typedDict)\n                {\n                    Console.WriteLine(&quot;\\&quot;&quot; + kvp.Key + &quot;\\&quot;: &quot; + kvp.Value);\n                }\n            }\n            else\n                Console.WriteLine(&quot;null&quot;);\n        }\n    }\n}\n</code></pre><p>输出：</p>\n<pre><code>Dictionary&lt;String, int&gt;\n&quot;One&quot;: 1\n&quot;Two&quot;: 2\n&quot;Three&quot;: 3\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(9,'.NET[C#]C#中如何使用从流(stream)中创建一个字节数组(byte[])？','<p>.NET[C#]C#中如何使用从流(stream)中创建一个字节数组(byte[])？</p>\n<h2 id=\"h2-u793Au4F8Bu4E00\"><a name=\"示例一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例一</h2><pre><code>public static byte[] ReadFully(Stream input)\n{\n    byte[] buffer = new byte[16*1024];\n    using (MemoryStream ms = new MemoryStream())\n    {\n        int read;\n        while ((read = input.Read(buffer, 0, buffer.Length)) &gt; 0)\n        {\n            ms.Write(buffer, 0, read);\n        }\n        return ms.ToArray();\n    }\n}\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E8C\"><a name=\"示例二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例二</h2><pre><code>public static byte[] ReadFully(Stream input)\n{\n    using (MemoryStream ms = new MemoryStream())\n    {\n        input.CopyTo(ms);\n        return ms.ToArray();\n    }\n}\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E09\"><a name=\"示例三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例三</h2><pre><code>MemoryStream ms = new MemoryStream();\nfile.PostedFile.InputStream.CopyTo(ms);\nvar byts = ms.ToArray();\nms.Dispose();\n</code></pre><h2 id=\"h2-u793Au4F8Bu56DB\"><a name=\"示例四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例四</h2><pre><code>public static class StreamHelpers\n{\n    public static byte[] ReadFully(this Stream input)\n    {\n        using (MemoryStream ms = new MemoryStream())\n        {\n            input.CopyTo(ms);\n            return ms.ToArray();\n        }\n    }\n}\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(10,'.NET[C#]使用一行LINQ语句实现字符串数组(string[])到数字数组(int[])的转换？','<p>.NET[C#]使用一行LINQ语句实现字符串数组(string[])到数字数组(int[])的转换？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如需有如下的字符串数组：</p>\n<pre><code>var arr = new string[] { &quot;1&quot;, &quot;2&quot;, &quot;3&quot;, &quot;4&quot; };\n</code></pre><p>如何使用一行LINQ语句实现字符串数组(string[])到数字数组(int[])的转换呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>使用 <em>Array.ConvertAll()</em> 方法：</p>\n<pre><code>int[] myInts = Array.ConvertAll(arr, s =&gt; int.Parse(s));\n</code></pre><p>或者更简洁的写法：</p>\n<pre><code>int[] myInts = Array.ConvertAll(arr, int.Parse);\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>int[] myInts = arr.Select(int.Parse).ToArray();\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>string[] arr = { null, &quot; &quot;, &quot; 1 &quot;, &quot; 002 &quot;, &quot;3.0&quot; };\nint i = 0; \nvar a = (from s in arr where int.TryParse(s, out i) select i).ToArray();  // a = { 1, 2 }\n</code></pre><p>或者</p>\n<pre><code>var a = arr.SelectMany(s =&gt; int.TryParse(s, out i) ? new[] { i } : new int[0]).ToArray();\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(11,'.NET[C#]LINQ查询中连接多个查询条件的方式有哪些？','<p>.NET[C#]LINQ查询中连接多个查询条件的方式有哪些？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如需要实现相似以下的 <em>LINQ</em> 查询语句：</p>\n<pre><code>var result = from x in entity\njoin y in entity2 \n       on x.field1 = y.field1 \nand \n          x.field2 = y.field2\n</code></pre><p>其中有多个查询条件，如何使用 <em>LINQ</em> 将多个条件连接起来，有哪些方式呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>var result = from x in entity\n   join y in entity2 on new { x.field1, x.field2 } equals new { y.field1, y.field2 }\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>entity.Join(entity2, x =&gt; new {x.Field1, x.Field2},\n                     y =&gt; new {y.Field1, y.Field2}, (x, y) =&gt; x);\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>var result = from x in entity1\n             from y in entity2\n                 .Where(y =&gt; y.field1 == x.field1 &amp;&amp; y.field2 == x.field2)\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(12,'.NET[C#]C#中如何调用基类的构造函数？','<h2 id=\"h2-u793Au4F8Bu4E00\"><a name=\"示例一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例一</h2><pre><code>public class MyExceptionClass : Exception\n{\n    public MyExceptionClass(string message, string extrainfo) : base(message)\n    {\n        //other stuff here\n    }\n}\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E8C\"><a name=\"示例二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例二</h2><pre><code>class MyExceptionClass : Exception\n{\n     public MyExceptionClass(string message, string extraInfo) : \n         base(ModifyMessage(message, extraInfo))\n     {\n     }\n\n     private static string ModifyMessage(string message, string extraInfo)\n     {\n         Trace.WriteLine(&quot;message was &quot; + message);\n         return message.ToLowerInvariant() + Environment.NewLine + extraInfo;\n     }\n}\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E09\"><a name=\"示例三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例三</h2><pre><code>\n\npublic class MyException : Exception\n{\n    public MyException() { }\n    public MyException(string msg) : base(msg) { }\n    public MyException(string msg, Exception inner) : base(msg, inner) { }\n}\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(13,'.NET[C#]C#中如何按照字典的值排序？','<p>.NET[C#]C#中如何按照字典的值排序？</p>\n<h2 id=\"h2-u793Au4F8Bu4E00\"><a name=\"示例一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例一</h2><p>.NET 2.0 以下版本：</p>\n<pre><code>using System.Linq.Enumerable;\n...\nList&lt;KeyValuePair&lt;string, string&gt;&gt; myList = aDictionary.ToList();\n\nmyList.Sort(\n    delegate(KeyValuePair&lt;string, string&gt; pair1,\n    KeyValuePair&lt;string, string&gt; pair2)\n    {\n        return pair1.Value.CompareTo(pair2.Value);\n    }\n);\n</code></pre><p>.NET 2.0 以上版本：</p>\n<pre><code>var myList = aDictionary.ToList();\n\nmyList.Sort((pair1,pair2) =&gt; pair1.Value.CompareTo(pair2.Value));\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E8C\"><a name=\"示例二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例二</h2><p>使用LINQ对字典排序：</p>\n<pre><code>Dictionary&lt;string, int&gt; myDict = new Dictionary&lt;string, int&gt;();\nmyDict.Add(&quot;one&quot;, 1);\nmyDict.Add(&quot;four&quot;, 4);\nmyDict.Add(&quot;two&quot;, 2);\nmyDict.Add(&quot;three&quot;, 3);\n\nvar sortedDict = from entry in myDict orderby entry.Value ascending select entry;\n</code></pre><h2 id=\"h2-u793Au4F8Bu4E09\"><a name=\"示例三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>示例三</h2><pre><code>var ordered = dict.OrderBy(x =&gt; x.Value).ToDictionary(x =&gt; x.Key, x =&gt; x.Value);\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(14,'.NET[C#]LINQ lambda表达式如何实现倒序排序(ORDER BY DESC)？','<p>.NET[C#]LINQ lambda表达式如何实现倒序排序(ORDER BY DESC)？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>可以使用 <code>OrderByDescending</code> 和 <code>ThenByDescending</code>来实现倒序排序，具体如下：</p>\n<p>非 <em>lambda</em> LINQ 查询中：</p>\n<pre><code>var query = from person in people\n            orderby person.Name descending, person.Age descending\n            select person.Name;\n</code></pre><p><em>lambda</em> LINQ 查询中：</p>\n<pre><code>var query = people.OrderByDescending(person =&gt; person.Name)\n                  .ThenByDescending(person =&gt; person.Age)\n                  .Select(person =&gt; person.Name);\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>List&lt;int&gt; list = new List&lt;int&gt;();\nlist.Add(1);\nlist.Add(5);\nlist.Add(4);\nlist.Add(3);\nlist.Add(2);\n\nforeach (var item in list.OrderByDescending(x =&gt; x))\n{\n    Console.WriteLine(item);                \n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>var qry = Employees\n          .OrderByDescending (s =&gt; s.EmpFName)\n          .ThenBy (s =&gt; s.Address)\n          .Select (s =&gt; s.EmpCode);\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(15,'.NET[C#]LINQ如何获取某个元素在集合中的索引位置？','<p>.NET[C#]LINQ如何获取某个元素在集合中的索引位置？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>如何查找到下列集合中最小值所在的索引位置：</p>\n<pre><code>{3,1,0,5}\n</code></pre><p>期望值为：<code>2</code></p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>public static int FindIndex&lt;T&gt;(this IEnumerable&lt;T&gt; items, Func&lt;T, bool&gt; predicate) {\n    if (items == null) throw new ArgumentNullException(&quot;items&quot;);\n    if (predicate == null) throw new ArgumentNullException(&quot;predicate&quot;);\n\n    int retVal = 0;\n    foreach (var item in items) {\n        if (predicate(item)) return retVal;\n        retVal++;\n    }\n    return -1;\n}\n\npublic static int IndexOf&lt;T&gt;(this IEnumerable&lt;T&gt; items, T item) { return items.FindIndex(i =&gt; EqualityComparer&lt;T&gt;.Default.Equals(item, i)); }\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>var list = new List&lt;int&gt; { 3, 1, 0, 5 };\nint pos = list.IndexOf(list.Min()); // returns 2\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>List&lt;int&gt; values = new List&lt;int&gt; { 3, 1, 0, 5 };\n\nint index =\n   values\n   .Select((n, i) =&gt; new { Value = n, Index = i })\n   .Aggregate((a,b) =&gt; a.Value &lt; b.Value ? a : b)\n   .Index;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>var list = new List&lt;int&gt; { 3, 1, 0, 5 };\nint pos = Enumerable.Range(0, list.Count)\n    .Aggregate((a, b) =&gt; (list[a] &lt; list[b]) ? a : b); // returns 2\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>int id = listMyObject.FindIndex(x =&gt; x.Id == 15);\n</code></pre><p>或者</p>\n<pre><code>int id = myEnumerator.ToList().FindIndex(x =&gt; x.Id == 15);\n</code></pre><p>或者</p>\n<pre><code>int id = myArray.ToList().FindIndex(x =&gt; x.Id == 15);\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>var data = new List&lt;int&gt; { 3, 1, 0, 5 };\n\nvar result = Enumerable.Range(0, data.Count).OrderBy(n =&gt; data[n]).First();\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(16,'.NET[C#]如何将LINQ查询出来的结果转换成字典(Dictionary)？','<p>.NET[C#]如何将LINQ查询出来的结果转换成字典(Dictionary)？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>如何实现类似这样的将LINQ查询的结果转换成字典：</p>\n<pre><code>Dictionary&lt;int, DateTime&gt; existingItems = \n    (from ObjType ot in TableObj\n        select (new KeyValuePair&lt;int, DateTime&gt;(ot.Key, ot.TimeStamp))\n    )\n</code></pre><h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>var dict = TableObj.Select( t =&gt; new { t.Key, t.TimeStamp } )\n                   .ToDictionary( t =&gt; t.Key, t =&gt; t.TimeStamp );\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>Dictionary&lt;int, DateTime&gt; existingItems = \n    (from ObjType ot in TableObj).ToDictionary(x =&gt; x.Key);\n</code></pre><p>或者</p>\n<pre><code>var dict = TableObj.ToDictionary(t =&gt; t.Key, t=&gt; t.TimeStamp);\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(17,'.NET[C#]LINQ如何实现NOT IN的查询？','<p>.NET[C#]使用LINQ如何将一个集合划分成多个子集合？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>NorthwindDataContext dc = new NorthwindDataContext();    \ndc.Log = Console.Out;\n\nvar query =    \n    from c in dc.Customers    \n    where !(from o in dc.Orders    \n            select o.CustomerID)    \n           .Contains(c.CustomerID)    \n    select c;\n\nforeach (var c in query) Console.WriteLine( c );\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>var answer = list1.Except(list2);\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>from item1 in List1\nwhere !(list2.Any(item2 =&gt; item2.Email == item1.Email))\nselect item1;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>var itemIds = inMemoryList.Select(x =&gt; x.Id).ToArray();\nvar otherObjects = context.ItemList.Where(x =&gt; !itemIds.Contains(x.Id));\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>var NotInRecord =list1.Where(p =&gt; !list2.Any(p2 =&gt; p2.Email  == p.Email));\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>list1.RemoveAll(Item =&gt; list2.Contains(Item));\n</code></pre><h2 id=\"h2-u65B9u6848u4E03\"><a name=\"方案七\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案七</h2><pre><code>var secondEmails = (from item in list2\n                    select new { Email = item.Email }\n                   ).ToList();\n\nvar matches = from item in list1\n              where !secondEmails.Contains(item.Email)\n              select new {Email = item.Email};\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(18,'.NET[C#]使用LINQ如何将一个集合划分成多个子集合？','<p>.NET[C#]使用LINQ如何将一个集合划分成多个子集合？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有如下的集合：</p>\n<pre><code>[a, g, e, w, p, s, q, f, x, y, i, m, c]\n</code></pre><p>需要划分后的结果：</p>\n<pre><code>[a, g, e], [w, p, s], [q, f, x], [y, i, m], [c]\n</code></pre><p>.NET[C#]使用LINQ如何实现以上子集合的划分呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>public static IList&lt;IList&lt;T&gt;&gt; Split&lt;T&gt;(IList&lt;T&gt; source)\n{\n    return  source\n        .Select((x, i) =&gt; new { Index = i, Value = x })\n        .GroupBy(x =&gt; x.Index / 3)\n        .Select(x =&gt; x.Select(v =&gt; v.Value).ToList())\n        .ToList();\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>public static IEnumerable&lt;IEnumerable&lt;T&gt;&gt; Chunk&lt;T&gt;(this IEnumerable&lt;T&gt; source, int chunksize)\n{\n    while (source.Any())\n    {\n        yield return source.Take(chunksize);\n        source = source.Skip(chunksize);\n    }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>public static IEnumerable&lt;IEnumerable&lt;T&gt;&gt; ChunkTrivialBetter&lt;T&gt;(this IEnumerable&lt;T&gt; source, int chunksize)\n{\n   var pos = 0; \n   while (source.Skip(pos).Any())\n   {\n      yield return source.Skip(pos).Take(chunksize);\n      pos += chunksize;\n   }\n}\n</code></pre><p>完整的功能代码：</p>\n<pre><code>namespace ChunkedEnumerator\n{\n    public static class Extensions \n    {\n        class ChunkedEnumerable&lt;T&gt; : IEnumerable&lt;T&gt;\n        {\n            class ChildEnumerator : IEnumerator&lt;T&gt;\n            {\n                ChunkedEnumerable&lt;T&gt; parent;\n                int position;\n                bool done = false;\n                T current;\n\n\n                public ChildEnumerator(ChunkedEnumerable&lt;T&gt; parent)\n                {\n                    this.parent = parent;\n                    position = -1;\n                    parent.wrapper.AddRef();\n                }\n\n                public T Current\n                {\n                    get\n                    {\n                        if (position == -1 || done)\n                        {\n                            throw new InvalidOperationException();\n                        }\n                        return current;\n\n                    }\n                }\n\n                public void Dispose()\n                {\n                    if (!done)\n                    {\n                        done = true;\n                        parent.wrapper.RemoveRef();\n                    }\n                }\n\n                object System.Collections.IEnumerator.Current\n                {\n                    get { return Current; }\n                }\n\n                public bool MoveNext()\n                {\n                    position++;\n\n                    if (position + 1 &gt; parent.chunkSize)\n                    {\n                        done = true;\n                    }\n\n                    if (!done)\n                    {\n                        done = !parent.wrapper.Get(position + parent.start, out current);\n                    }\n\n                    return !done;\n\n                }\n\n                public void Reset()\n                {\n                    // per http://msdn.microsoft.com/en-us/library/system.collections.ienumerator.reset.aspx\n                    throw new NotSupportedException();\n                }\n            }\n\n            EnumeratorWrapper&lt;T&gt; wrapper;\n            int chunkSize;\n            int start;\n\n            public ChunkedEnumerable(EnumeratorWrapper&lt;T&gt; wrapper, int chunkSize, int start)\n            {\n                this.wrapper = wrapper;\n                this.chunkSize = chunkSize;\n                this.start = start;\n            }\n\n            public IEnumerator&lt;T&gt; GetEnumerator()\n            {\n                return new ChildEnumerator(this);\n            }\n\n            System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()\n            {\n                return GetEnumerator();\n            }\n\n        }\n\n        class EnumeratorWrapper&lt;T&gt;\n        {\n            public EnumeratorWrapper (IEnumerable&lt;T&gt; source)\n            {\n                SourceEumerable = source;\n            }\n            IEnumerable&lt;T&gt; SourceEumerable {get; set;}\n\n            Enumeration currentEnumeration;\n\n            class Enumeration\n            {\n                public IEnumerator&lt;T&gt; Source { get; set; }\n                public int Position { get; set; }\n                public bool AtEnd { get; set; }\n            }\n\n            public bool Get(int pos, out T item) \n            {\n\n                if (currentEnumeration != null &amp;&amp; currentEnumeration.Position &gt; pos)\n                {\n                    currentEnumeration.Source.Dispose();\n                    currentEnumeration = null;\n                }\n\n                if (currentEnumeration == null)\n                {\n                    currentEnumeration = new Enumeration { Position = -1, Source = SourceEumerable.GetEnumerator(), AtEnd = false };\n                }\n\n                item = default(T);\n                if (currentEnumeration.AtEnd)\n                {\n                    return false;\n                }\n\n                while(currentEnumeration.Position &lt; pos) \n                {\n                    currentEnumeration.AtEnd = !currentEnumeration.Source.MoveNext();\n                    currentEnumeration.Position++;\n\n                    if (currentEnumeration.AtEnd) \n                    {\n                        return false;\n                    }\n\n                }\n\n                item = currentEnumeration.Source.Current;\n\n                return true;\n            }\n\n            int refs = 0;\n\n            // needed for dispose semantics \n            public void AddRef()\n            {\n                refs++;\n            }\n\n            public void RemoveRef()\n            {\n                refs--;\n                if (refs == 0 &amp;&amp; currentEnumeration != null)\n                {\n                    var copy = currentEnumeration;\n                    currentEnumeration = null;\n                    copy.Source.Dispose();\n                }\n            }\n        }\n\n        public static IEnumerable&lt;IEnumerable&lt;T&gt;&gt; Chunk&lt;T&gt;(this IEnumerable&lt;T&gt; source, int chunksize)\n        {\n            if (chunksize &lt; 1) throw new InvalidOperationException();\n\n            var wrapper =  new EnumeratorWrapper&lt;T&gt;(source);\n\n            int currentPos = 0;\n            T ignore;\n            try\n            {\n                wrapper.AddRef();\n                while (wrapper.Get(currentPos, out ignore))\n                {\n                    yield return new ChunkedEnumerable&lt;T&gt;(wrapper, chunksize, currentPos);\n                    currentPos += chunksize;\n                }\n            }\n            finally\n            {\n                wrapper.RemoveRef();\n            }\n        }\n    }\n\n    class Program\n    {\n        static void Main(string[] args)\n        {\n            int i = 10;\n            foreach (var group in Enumerable.Range(1, int.MaxValue).Skip(10000000).Chunk(3))\n            {\n                foreach (var n in group)\n                {\n                    Console.Write(n);\n                    Console.Write(&quot; &quot;);\n                }\n                Console.WriteLine();\n                if (i-- == 0) break;\n            }\n\n\n            var stuffs = Enumerable.Range(1, 10).Chunk(2).ToArray();\n\n            foreach (var idx in new [] {3,2,1})\n            {\n                Console.Write(&quot;idx &quot; + idx + &quot; &quot;);\n                foreach (var n in stuffs[idx])\n                {\n                    Console.Write(n);\n                    Console.Write(&quot; &quot;);\n                }\n                Console.WriteLine();\n            }\n\n            /*\n\n10000001 10000002 10000003\n10000004 10000005 10000006\n10000007 10000008 10000009\n10000010 10000011 10000012\n10000013 10000014 10000015\n10000016 10000017 10000018\n10000019 10000020 10000021\n10000022 10000023 10000024\n10000025 10000026 10000027\n10000028 10000029 10000030\n10000031 10000032 10000033\nidx 3 7 8\nidx 2 5 6\nidx 1 3 4\n             */\n\n            Console.ReadKey();\n\n\n        }\n\n    }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>public static IEnumerable&lt;IEnumerable&lt;T&gt;&gt; GetEnumerableOfEnumerables&lt;T&gt;(\n  IEnumerable&lt;T&gt; enumerable, int groupSize)\n{\n   // The list to return.\n   List&lt;T&gt; list = new List&lt;T&gt;(groupSize);\n\n   // Cycle through all of the items.\n   foreach (T item in enumerable)\n   {\n     // Add the item.\n     list.Add(item);\n\n     // If the list has the number of elements, return that.\n     if (list.Count == groupSize)\n     {\n       // Return the list.\n       yield return list;\n\n       // Set the list to a new list.\n       list = new List&lt;T&gt;(groupSize);\n     }\n   }\n\n   // Return the remainder if there is any,\n   if (list.Count != 0)\n   {\n     // Return the list.\n     yield return list;\n   }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>public static IEnumerable&lt;IEnumerable&lt;T&gt;&gt; Chunks&lt;T&gt;(this IEnumerable&lt;T&gt; enumerable,\n                                                    int chunkSize)\n{\n    if (chunkSize &lt; 1) throw new ArgumentException(&quot;chunkSize must be positive&quot;);\n\n    using (var e = enumerable.GetEnumerator())\n    while (e.MoveNext())\n    {\n        var remaining = chunkSize;    // elements remaining in the current chunk\n        var innerMoveNext = new Func&lt;bool&gt;(() =&gt; --remaining &gt; 0 &amp;&amp; e.MoveNext());\n\n        yield return e.GetChunk(innerMoveNext);\n        while (innerMoveNext()) {/* discard elements skipped by inner iterator */}\n    }\n}\n\nprivate static IEnumerable&lt;T&gt; GetChunk&lt;T&gt;(this IEnumerator&lt;T&gt; e,\n                                          Func&lt;bool&gt; innerMoveNext)\n{\n    do yield return e.Current;\n    while (innerMoveNext());\n}\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>var src = new [] {1, 2, 3, 4, 5, 6}; \n\nvar c3 = src.Chunks(3);      // {{1, 2, 3}, {4, 5, 6}}; \nvar c4 = src.Chunks(4);      // {{1, 2, 3, 4}, {5, 6}}; \n\nvar sum   = c3.Select(c =&gt; c.Sum());    // {6, 15}\nvar count = c3.Count();                 // 2\nvar take2 = c3.Select(c =&gt; c.Take(2));  // {{1, 2}, {4, 5}}\n</code></pre><h2 id=\"h2-u65B9u6848u4E03\"><a name=\"方案七\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案七</h2><pre><code>public static class EnumerableExtensions\n{\n  public static IEnumerable&lt;IEnumerable&lt;T&gt;&gt; Split&lt;T&gt;(this IEnumerable&lt;T&gt; source, int len)\n  {\n     if (len == 0)\n        throw new ArgumentNullException();\n\n     var enumer = source.GetEnumerator();\n     while (enumer.MoveNext())\n     {\n        yield return Take(enumer.Current, enumer, len);\n     }\n  }\n\n  private static IEnumerable&lt;T&gt; Take&lt;T&gt;(T head, IEnumerator&lt;T&gt; tail, int len)\n  {\n     while (true)\n     {\n        yield return head;\n        if (--len == 0)\n           break;\n        if (tail.MoveNext())\n           head = tail.Current;\n        else\n           break;\n     }\n  }\n}\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(19,'[SQL Server]SQL Server数据库中删除表的数据之后如何重置自增列的标识？','<p>[SQL Server]SQL Server数据库中删除表的数据之后如何重置自增列的标识？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>使用 <em>DBCC CHECKIDENT</em> ，语法如下：</p>\n<pre><code>DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])\n[ WITH NO_INFOMSGS ]\n</code></pre><p>使用方法如下：</p>\n<pre><code>DBCC CHECKIDENT (&#39;[TestTable]&#39;, RESEED, 0);\nGO\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>你也可以使用 <em>TRUNCATE</em> 关键字来删除表的数据，如：</p>\n<pre><code>TRUNCATE TABLE [MyTable];\n</code></pre><p>这条语句相当于：</p>\n<pre><code>DELETE FROM [MyTable];\nDBCC CHECKIDENT (&#39;[MyTable]&#39;, RESEED, 0);\n</code></pre><blockquote>\n<p><strong>特别注意：</strong> TRUNCATE 关键字会删除指定表中所有数据，请慎用。</p>\n</blockquote>\n',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(20,'.NET[C#]LINQ中LEFT OUTER JOIN 如何实现？','<p>.NET[C#]LINQ中LEFT OUTER JOIN 如何实现？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>C# LINQ中怎样实现 LEFT OUTER JOIN的查询（不使用join-on-equals-into语法的情况下），INNER JOIN的实现如下：</p>\n<pre><code>List&lt;JoinPair&gt; innerFinal = (from l in lefts from r in rights where l.Key == r.Key\n                             select new JoinPair { LeftId = l.Id, RightId = r.Id})\n</code></pre><p>但以下的OUTER JOIN 不是错误的：</p>\n<pre><code>List&lt; JoinPair&gt; leftFinal = (from l in lefts from r in rights\n                             select new JoinPair { \n                                            LeftId = l.Id, \n                                            RightId = ((l.Key==r.Key) ? r.Id : 0\n                                        })\n</code></pre><p>其中用到的 <em>JoinPair</em> 实体类：</p>\n<pre><code>public class JoinPair { long leftId; long rightId; }\n</code></pre><p>要如何实现以上的OUTER JOIN语句呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>var q =\n    from c in categories\n    join p in products on c.Category equals p.Category into ps\n    from p in ps.DefaultIfEmpty()\n    select new { Category = c, ProductName = p == null ? &quot;(No products)&quot; : p.ProductName };\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>使用lambda表达式：</p>\n<pre><code>db.Categories    \n  .GroupJoin(\n      db.Products,\n      Category =&gt; Category.CategoryId,\n      Product =&gt; Product.CategoryId,\n      (x, y) =&gt; new { Category = x, Products = y })\n  .SelectMany(\n      xy =&gt; xy.Products.DefaultIfEmpty(),\n      (x, y) =&gt; new { Category = x.Category, Product = y })\n  .Select(s =&gt; new\n  {\n      CategoryName = s.Category.Name,     \n      ProductName = s.Product.Name   \n  })\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>var leftFinal =\n        from l in lefts\n        join r in rights on l equals r.Left into lrs\n        from lr in lrs.DefaultIfEmpty()\n        select new { LeftId = l.Id, RightId = ((l.Key==r.Key) ? r.Id : 0 };\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><p>实现一个静态扩展方法：</p>\n<pre><code>public static IEnumerable&lt;Result&gt; LeftJoin&lt;TOuter, TInner, TKey, Result&gt;(\n  this IEnumerable&lt;TOuter&gt; outer, IEnumerable&lt;TInner&gt; inner\n  , Func&lt;TOuter, TKey&gt; outerKeySelector, Func&lt;TInner, TKey&gt; innerKeySelector\n  , Func&lt;TOuter, TInner, Result&gt; resultSelector, IEqualityComparer&lt;TKey&gt; comparer)\n  {\n    if (outer == null)\n      throw new ArgumentException(&quot;outer&quot;);\n\n    if (inner == null)\n      throw new ArgumentException(&quot;inner&quot;);\n\n    if (outerKeySelector == null)\n      throw new ArgumentException(&quot;outerKeySelector&quot;);\n\n    if (innerKeySelector == null)\n      throw new ArgumentException(&quot;innerKeySelector&quot;);\n\n    if (resultSelector == null)\n      throw new ArgumentException(&quot;resultSelector&quot;);\n\n    return LeftJoinImpl(outer, inner, outerKeySelector, innerKeySelector, resultSelector, comparer ?? EqualityComparer&lt;TKey&gt;.Default);\n  }\n\n  static IEnumerable&lt;Result&gt; LeftJoinImpl&lt;TOuter, TInner, TKey, Result&gt;(\n      IEnumerable&lt;TOuter&gt; outer, IEnumerable&lt;TInner&gt; inner\n      , Func&lt;TOuter, TKey&gt; outerKeySelector, Func&lt;TInner, TKey&gt; innerKeySelector\n      , Func&lt;TOuter, TInner, Result&gt; resultSelector, IEqualityComparer&lt;TKey&gt; comparer)\n  {\n    var innerLookup = inner.ToLookup(innerKeySelector, comparer);\n\n    foreach (var outerElment in outer)\n    {\n      var outerKey = outerKeySelector(outerElment);\n      var innerElements = innerLookup[outerKey];\n\n      if (innerElements.Any())\n        foreach (var innerElement in innerElements)\n          yield return resultSelector(outerElment, innerElement);\n      else\n        yield return resultSelector(outerElment, default(TInner));\n     }\n   }\n</code></pre><p>调用方法：</p>\n<pre><code>static void Main(string[] args)\n{\n    var inner = new[] { Tuple.Create(1, &quot;1&quot;), Tuple.Create(2, &quot;2&quot;), Tuple.Create(3, &quot;3&quot;) };\n    var outer = new[] { Tuple.Create(1, &quot;11&quot;), Tuple.Create(2, &quot;22&quot;) };\n\n   var res = outer.LeftJoin(inner, item =&gt; item.Item1, item =&gt; item.Item1, (it1, it2) =&gt;\n   new { Key = it1.Item1, V1 = it1.Item2, V2 = it2 != null ? it2.Item2 : default(string) });\n\n   foreach (var item in res)\n     Console.WriteLine(string.Format(&quot;{0}, {1}, {2}&quot;, item.Key, item.V1, item.V2));\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>from d in context.dc_tpatient_bookingd\njoin bookingm in context.dc_tpatient_bookingm \n     on d.bookingid equals bookingm.bookingid into bookingmGroup\nfrom m in bookingmGroup.DefaultIfEmpty()\njoin patient in dc_tpatient\n     on m.prid equals patient.prid into patientGroup\nfrom p in patientGroup.DefaultIfEmpty()\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>List&lt;Person&gt; persons = new List&lt;Person&gt;\n{\n    new Person { id = 1, name = &quot;Alex&quot;, phone = &quot;4235234&quot; },\n    new Person { id = 2, name = &quot;Bob&quot;, phone = &quot;0014352&quot; },\n    new Person { id = 3, name = &quot;Sam&quot;, phone = &quot;1345&quot; },\n    new Person { id = 4, name = &quot;Den&quot;, phone = &quot;3453452&quot; },\n    new Person { id = 5, name = &quot;Alen&quot;, phone = &quot;0353012&quot; },\n    new Person { id = 6, name = &quot;Simon&quot;, phone = &quot;0353012&quot; }\n};\n\nList&lt;School&gt; schools = new List&lt;School&gt;\n{\n    new School { id = 1, name = &quot;Saint. John&#39;s school&quot;},\n    new School { id = 2, name = &quot;Public School 200&quot;},\n    new School { id = 3, name = &quot;Public School 203&quot;}\n};\n\nList&lt;PersonSchool&gt; persons_schools = new List&lt;PersonSchool&gt;\n{\n    new PersonSchool{id_person = 1, id_school = 1},\n    new PersonSchool{id_person = 2, id_school = 2},\n    new PersonSchool{id_person = 3, id_school = 3},\n    new PersonSchool{id_person = 4, id_school = 1},\n    new PersonSchool{id_person = 5, id_school = 2}\n    //a relation to the person with id=6 is absent\n};\n\nvar query = from person in persons\n            join person_school in persons_schools on person.id equals person_school.id_person\n            into persons_schools_joined\n            from person_school_joined in persons_schools_joined.DefaultIfEmpty()\n            from school in schools.Where(var_school =&gt; person_school_joined == null ? false : var_school.id == person_school_joined.id_school).DefaultIfEmpty()\n            select new { Person = person.name, School = school == null ? String.Empty : school.name };\n\nforeach (var elem in query)\n{\n    System.Console.WriteLine(&quot;{0},{1}&quot;, elem.Person, elem.School);\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E03\"><a name=\"方案七\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案七</h2><pre><code>class Person\n{\n    public int ID { get; set; }\n    public string FirstName { get; set; }\n    public string LastName { get; set; }\n    public string Phone { get; set; }\n}\n\nclass Pet\n{\n    public string Name { get; set; }\n    public Person Owner { get; set; }\n}\n\npublic static void LeftOuterJoinExample()\n{\n    Person magnus = new Person {ID = 1, FirstName = &quot;Magnus&quot;, LastName = &quot;Hedlund&quot;};\n    Person terry = new Person {ID = 2, FirstName = &quot;Terry&quot;, LastName = &quot;Adams&quot;};\n    Person charlotte = new Person {ID = 3, FirstName = &quot;Charlotte&quot;, LastName = &quot;Weiss&quot;};\n    Person arlene = new Person {ID = 4, FirstName = &quot;Arlene&quot;, LastName = &quot;Huff&quot;};\n\n    Pet barley = new Pet {Name = &quot;Barley&quot;, Owner = terry};\n    Pet boots = new Pet {Name = &quot;Boots&quot;, Owner = terry};\n    Pet whiskers = new Pet {Name = &quot;Whiskers&quot;, Owner = charlotte};\n    Pet bluemoon = new Pet {Name = &quot;Blue Moon&quot;, Owner = terry};\n    Pet daisy = new Pet {Name = &quot;Daisy&quot;, Owner = magnus};\n\n    // Create two lists.\n    List&lt;Person&gt; people = new List&lt;Person&gt; {magnus, terry, charlotte, arlene};\n    List&lt;Pet&gt; pets = new List&lt;Pet&gt; {barley, boots, whiskers, bluemoon, daisy};\n\n    var query = from person in people\n        where person.ID == 4\n        join pet in pets on person equals pet.Owner  into personpets\n        from petOrNull in personpets.DefaultIfEmpty()\n        select new { Person=person, Pet = petOrNull}; \n\n\n\n    foreach (var v in query )\n    {\n        Console.WriteLine(&quot;{0,-15}{1}&quot;, v.Person.FirstName + &quot;:&quot;, (v.Pet == null ? &quot;Does not Exist&quot; : v.Pet.Name));\n    }\n}\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(21,'.NET[C#]LINQ中如何找出在一个集合List<>中但不在另一个集合List<>中的所有元素集合？','<p>.NET[C#]LINQ中如何找出在一个集合List&lt;&gt;中但不在另一个集合List&lt;&gt;中的所有元素集合？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有以下示例程序代码：</p>\n<pre><code>class Program\n{\n    static void Main(string[] args)\n    {\n        List&lt;Person&gt; peopleList1 = new List&lt;Person&gt;();\n        peopleList1.Add(new Person() { ID = 1 });\n        peopleList1.Add(new Person() { ID = 2 });\n        peopleList1.Add(new Person() { ID = 3 });\n\n        List&lt;Person&gt; peopleList2 = new List&lt;Person&gt;();\n        peopleList2.Add(new Person() { ID = 1 });\n        peopleList2.Add(new Person() { ID = 2 });\n        peopleList2.Add(new Person() { ID = 3 });\n        peopleList2.Add(new Person() { ID = 4 });\n        peopleList2.Add(new Person() { ID = 5 });\n\n        //此处需要找出所有在 &#39;peopleList2&#39; 但不在 &#39;peopleList1&#39; 集合中的其他集合\n        //此例中应该返回集合对象分别为：ID = 4 ， ID = 5\n    }\n}\n\nclass Person\n{\n    public int ID { get; set; }\n}\n</code></pre><p>使用LINQ来做查询，应该如何实现呢？</p>\n<h2 id=\"h2--where-\"><a name=\"方案一、Where()\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一、Where()</h2><pre><code>var result = peopleList2.Where(p =&gt; !peopleList1.Any(p2 =&gt; p2.ID == p.ID));\n</code></pre><h2 id=\"h2--except-\"><a name=\"方案二、Except()\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二、Except()</h2><pre><code>peopleList2.Except(peopleList1)\n</code></pre><h2 id=\"h2--linq-query\"><a name=\"方案三、LINQ QUERY\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三、LINQ QUERY</h2><pre><code>var peopleDifference = \n  from person2 in peopleList2\n  where !(\n      from person1 in peopleList1 \n      select person1.ID\n    ).Contains(person2.ID)\n  select person2;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>var result = peopleList2.Where(p =&gt; peopleList1.All(p2 =&gt; p2.ID != p.ID));\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>List&lt;string&gt; list1 = new List&lt;string&gt;() { &quot;1&quot;, &quot;2&quot;, &quot;3&quot; };\nList&lt;string&gt; list2 = new List&lt;string&gt;() { &quot;2&quot;, &quot;4&quot; };\n\nList&lt;string&gt; inList1ButNotList2 = (from o in list1\n                                   join p in list2 on o equals p into t\n                                   from od in t.DefaultIfEmpty()\n                                   where od == null\n                                   select o).ToList&lt;string&gt;();\n\nList&lt;string&gt; inList2ButNotList1 = (from o in list2\n                                   join p in list1 on o equals p into t\n                                   from od in t.DefaultIfEmpty()\n                                   where od == null\n                                   select o).ToList&lt;string&gt;();\n\nList&lt;string&gt; inBoth = (from o in list1\n                       join p in list2 on o equals p into t\n                       from od in t.DefaultIfEmpty()\n                       where od != null\n                       select od).ToList&lt;string&gt;();\n</code></pre><h2 id=\"h2-u65B9u6848u516D\"><a name=\"方案六\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案六</h2><pre><code>public static class EnumerableExtensions\n{\n    public static IEnumerable&lt;TSource&gt; Exclude&lt;TSource, TKey&gt;(this IEnumerable&lt;TSource&gt; source,\n    IEnumerable&lt;TSource&gt; exclude, Func&lt;TSource, TKey&gt; keySelector)\n    {\n       var excludedSet = new HashSet&lt;TKey&gt;(exclude.Select(keySelector));\n       return source.Where(item =&gt; !excludedSet.Contains(keySelector(item)));\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>list1.Exclude(list2, i =&gt; i.ID);\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(22,'.NET[C#]LINQ中如何实现SQL中的LIKE语句查询条件？','<p>.NET[C#]LINQ中如何实现SQL中的LIKE语句查询条件？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有以下的LINQ查询：</p>\n<pre><code>SELECT O.Id, O.Name as Organization\nFROM Organizations O\nJOIN OrganizationsHierarchy OH ON O.Id=OH.OrganizationsId\nwhere OH.Hierarchy like &#39;%/12/%&#39;\n</code></pre><p>在LINQ中如何实现</p>\n<pre><code>where OH.Hierarchy like &#39;%/12/%&#39;\n</code></pre><p>这样的查询条件呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>.Where(oh =&gt; oh.Hierarchy.Contains(&quot;/12/&quot;))\n</code></pre><p>你也可以使用</p>\n<pre><code>.Where(oh =&gt; oh.Hierarchy.StartWith(&quot;/12/&quot;))\n</code></pre><p>或者</p>\n<pre><code>.Where(oh =&gt; oh.Hierarchy.EndWith(&quot;/12/&quot;))\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>var result = from c in SampleList\nwhere c.LongName.IndexOf(SearchQuery) &gt;= 0\nselect c;\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(23,'[SQL Server]SQL Server数据库使用指定分割符分割字符串并按索引返回子字符串？','<p>[SQL Server]SQL Server数据库使用指定分割符分割字符串并按索引返回子字符串？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有字符串 “1|20|3|343|44|6|8765”，在SQL Server 中如何实现按竖线[|]分割这个字符串，并返回对应索引的子字符串呢，比如索引为1时，子字符串为:20 ？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>Declare @products varchar(200) = &#39;1|20|3|343|44|6|8765&#39;\nDeclare @individual varchar(20) = null\n\nWHILE LEN(@products) &gt; 0\nBEGIN\n    IF PATINDEX(&#39;%|%&#39;, @products) &gt; 0\n    BEGIN\n        SET @individual = SUBSTRING(@products,\n                                    0,\n                                    PATINDEX(&#39;%|%&#39;, @products))\n        SELECT @individual\n\n        SET @products = SUBSTRING(@products,\n                                  LEN(@individual + &#39;|&#39;) + 1,\n                                  LEN(@products))\n    END\n    ELSE\n    BEGIN\n        SET @individual = @products\n        SET @products = NULL\n        SELECT @individual\n    END\nEND\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>create function dbo.SplitString \n(\n    @str nvarchar(max), \n    @separator char(1)\n)\nreturns table\nAS\nreturn (\nwith tokens(p, a, b) AS (\n    select \n        cast(1 as bigint), \n        cast(1 as bigint), \n        charindex(@separator, @str)\n    union all\n    select\n        p + 1, \n        b + 1, \n        charindex(@separator, @str, b + 1)\n    from tokens\n    where b &gt; 0\n)\nselect\n    p-1 ItemIndex,\n    substring(\n        @str, \n        a, \n        case when b &gt; 0 then b-a ELSE LEN(@str) end) \n    AS s\nfrom tokens\n);\n\nGO\n</code></pre><p>调用方式：</p>\n<pre><code>select s \nfrom dbo.SplitString(&#39;Hello John Smith&#39;, &#39; &#39;)\nwhere zeroBasedOccurance=1\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>CREATE FUNCTION [dbo].[SplitString]\n    (\n        @List NVARCHAR(MAX),\n        @Delim VARCHAR(255)\n    )\n    RETURNS TABLE\n    AS\n        RETURN ( SELECT [Value] FROM \n          ( \n            SELECT \n              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],\n              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))\n            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)\n              FROM sys.all_objects) AS x\n              WHERE Number &lt;= LEN(@List)\n              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim\n          ) AS y\n        );\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>CREATE FUNCTION SplitString \n(\n    -- Add the parameters for the function here\n    @myString varchar(500),\n    @deliminator varchar(10)\n)\nRETURNS \n@ReturnTable TABLE \n(\n    -- Add the column definitions for the TABLE variable here\n    [id] [int] IDENTITY(1,1) NOT NULL,\n    [part] [varchar](50) NULL\n)\nAS\nBEGIN\n        Declare @iSpaces int\n        Declare @part varchar(50)\n\n        --initialize spaces\n        Select @iSpaces = charindex(@deliminator,@myString,0)\n        While @iSpaces &gt; 0\n\n        Begin\n            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))\n\n            Insert Into @ReturnTable(part)\n            Select @part\n\n    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(&#39; &#39;,@myString,0))\n\n\n            Select @iSpaces = charindex(@deliminator,@myString,0)\n        end\n\n        If len(@myString) &gt; 0\n            Insert Into @ReturnTable\n            Select @myString\n\n    RETURN \nEND\nGO\n</code></pre><p>调用方式：</p>\n<pre><code>Select * From SplitString(&#39;Hello John Smith&#39;,&#39; &#39;)\n</code></pre><h2 id=\"h2-u65B9u6848u4E94\"><a name=\"方案五\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案五</h2><pre><code>CREATE FUNCTION [dbo].[split](\n          @delimited NVARCHAR(MAX),\n          @delimiter NVARCHAR(100)\n        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))\n        AS\n        BEGIN\n          DECLARE @xml XML\n          SET @xml = N&#39;&lt;t&gt;&#39; + REPLACE(@delimited,@delimiter,&#39;&lt;/t&gt;&lt;t&gt;&#39;) + &#39;&lt;/t&gt;&#39;\n\n          INSERT INTO @t(val)\n          SELECT  r.value(&#39;.&#39;,&#39;varchar(MAX)&#39;) as item\n          FROM  @xml.nodes(&#39;/t&#39;) as records(r)\n          RETURN\n        END\n</code></pre><p>调用方式：</p>\n<pre><code>select * from dbo.split(&#39;Hello John Smith&#39;,&#39; &#39;)\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(24,'.NET[C#]使用LINQ从List<T>集合中删除指定集合元素？','<p>.NET[C#]使用LINQ从List&lt;T&gt;集合中删除指定集合元素？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>实体类(Author)：</p>\n<pre><code>public class Author{\n    public int id{get;set;}\n   public string firstname{get;set;}\n   public string lastname {get;set;}\n}\n</code></pre><p>比如有如下的LINQ查询语句：</p>\n<pre><code>var authors = from x in authorsList\n              select x;\n</code></pre><p>其中，需要从查询集合中删除 <em>firstname=’Bob’</em> 的所有用户，应该如何实现呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>authorsList = authorsList.Where(x =&gt; x.FirstName != &quot;Bob&quot;).ToList();\n</code></pre><p>或者</p>\n<pre><code>authorsList.RemoveAll(x =&gt; x.firstname == &quot;Bob&quot;);\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>var authorsList = new List&lt;Author&gt;()\n{\n    new Author{ Firstname = &quot;Bob&quot;, Lastname = &quot;Smith&quot; },\n    new Author{ Firstname = &quot;Fred&quot;, Lastname = &quot;Jones&quot; },\n    new Author{ Firstname = &quot;Brian&quot;, Lastname = &quot;Brains&quot; },\n    new Author{ Firstname = &quot;Billy&quot;, Lastname = &quot;TheKid&quot; }\n};\n\nvar authors = authorsList.Where(a =&gt; a.Firstname == &quot;Bob&quot;);\nauthorsList = authorsList.Except(authors).ToList();\nauthorsList = authorsList.Except(authorsList.Where(a=&gt;a.Firstname==&quot;Billy&quot;)).ToList();\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(25,'[SQL Server]SQL Server数据库中日期格式化只保留年月日部分的方法有哪些？','<p>[SQL Server]SQL Server数据库中日期格式化只保留年月日部分的方法有哪些？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>SQL Server 2008:</p>\n<pre><code>CONVERT(DATE, getdate(), 101)\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(26,'[SQL Server]SQL Server数据库中判断临时表是否存在并删除后创建的方法有哪些？','<p>[SQL Server]SQL Server数据库中判断临时表是否存在并删除后创建的方法有哪些？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>IF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL DROP TABLE #Results\nGO\nCREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )\nGO\nselect company, stepid, fieldid from #Results\nGO\nALTER TABLE #Results ADD foo VARCHAR(50) NULL\nGO\nselect company, stepid, fieldid, foo from #Results\nGO\nIF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL DROP TABLE #Results\nGO\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>IF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL\n    Truncate TABLE #Results\nelse\n    CREATE TABLE #Results\n    (\n        Company             CHAR(3),\n        StepId              TINYINT,\n        FieldId             TINYINT,\n    )\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>if exists (\n    select  * from tempdb.dbo.sysobjects o\n    where o.xtype in (&#39;U&#39;) \n\n   and o.id = object_id(N&#39;tempdb..#tempTable&#39;)\n)\nDROP TABLE #tempTable;\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>IF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL\n    DROP TABLE #Results\n\nCREATE TABLE #Results\n(\n    Company                CHAR(3),\n    StepId                TINYINT,\n    FieldId                TINYINT,\n)\n\nGO\n\nselect company, stepid, fieldid from #Results\n\nIF OBJECT_ID(&#39;tempdb..#Results&#39;) IS NOT NULL\nDROP TABLE #Results\n\nCREATE TABLE #Results\n(\n    Company                CHAR(3),\n    StepId                TINYINT,\n    FieldId                TINYINT,\n    NewColumn            NVARCHAR(50)\n)\n\nGO\n\nselect company, stepid, fieldid, NewColumn from #Results\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(27,'[SQL Server]SQL Server数据库中判断数据存在则更新，不存在则插入数据的方法有哪些？','<p>[SQL Server]SQL Server数据库中判断数据存在则更新，不存在则插入数据的方法有哪些？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>在很多项目中，我们有这样的需求：在向数据表中插入数据之前，需要先判断这条数据是否存在，如果存在，则更新，如果不存在，则插入新数据，实现方案有哪些呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>begin tran\nif exists (select * from table with (updlock,serializable) where key = @key)\nbegin\n   update table set ...\n   where key = @key\nend\nelse\nbegin\n   insert into table (key, ...)\n   values (@key, ...)\nend\ncommit tran\n</code></pre><p>或者</p>\n<pre><code>begin tran\n   update table with (serializable) set ...\n   where key = @key\n\n   if @@rowcount = 0\n   begin\n      insert into table (key, ...) values (@key,..)\n   end\ncommit tran\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p><em>SQL Server 2008</em> 或者以上版本，可以使用MERGE关键字：</p>\n<pre><code>merge tablename with(HOLDLOCK) as target\nusing (values (&#39;new value&#39;, &#39;different value&#39;))\n    as source (field1, field2)\n    on target.idfield = 7\nwhen matched then\n    update\n    set field1 = source.field1,\n        field2 = source.field2,\n        ...\nwhen not matched then\n    insert ( idfield, field1, field2, ... )\n    values ( 7,  source.field1, source.field2, ... )\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)\nUPDATE [Table] SET propertyOne = propOne, property2 . . .\nELSE\nINSERT INTO [Table] (propOne, propTwo . . .)\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)\nWHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]\nWHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(28,'.NET[C#]LINQ将List<string>集合使用连接符连接成单个字符串？','<p>.NET[C#]LINQ将List&lt;string&gt;集合使用连接符连接成单个字符串？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有字符串集合：</p>\n<pre><code>List&lt;string&gt; items = new List&lt;string&gt;() { &quot;foo&quot;, &quot;boo&quot;, &quot;john&quot;, &quot;doe&quot; };\n</code></pre><p>如何使用LINQ将其连接成：<code>foo,boo,john,doe</code> 的单个字符串呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>string delimiter = &quot;,&quot;;\nList&lt;string&gt; items = new List&lt;string&gt;() { &quot;foo&quot;, &quot;boo&quot;, &quot;john&quot;, &quot;doe&quot; };\nConsole.WriteLine(items.Aggregate((i, j) =&gt; i + delimiter + j));\n</code></pre><p>示例程序：</p>\n<p>实体类：</p>\n<pre><code>public class Foo\n{\n    public string Boo { get; set; }\n}\n</code></pre><p>示例用法：</p>\n<pre><code>class Program\n{\n    static void Main(string[] args)\n    {\n        string delimiter = &quot;,&quot;;\n        List&lt;Foo&gt; items = new List&lt;Foo&gt;() { new Foo { Boo = &quot;ABC&quot; }, new Foo { Boo = &quot;DEF&quot; },\n            new Foo { Boo = &quot;GHI&quot; }, new Foo { Boo = &quot;JKL&quot; } };\n\n        Console.WriteLine(items.Aggregate((i, j) =&gt; new Foo{Boo = (i.Boo + delimiter + j.Boo)}).Boo);\n        Console.ReadKey();\n\n    }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>.NET 4.0 或者以上版本</p>\n<pre><code>var str = string.Join(delimiter, list);\n</code></pre><p>.NET 4.0以下的版本：</p>\n<pre><code>var str = string.Join(delimiter, list.ToArray());\n</code></pre><p>或者</p>\n<pre><code>var str = string.Join(delimiter, list.Select(i =&gt; i.Boo).ToArray());\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(29,'.NET[C#]LINQ中IEnumerable<T>如何实现ForEach的遍历？','<p>.NET[C#]LINQ中IEnumerable&lt;T&gt;如何实现ForEach的遍历？</p>\n<h2 id=\"h2-u95EEu9898u63CFu8FF0\"><a name=\"问题描述\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>问题描述</h2><p>比如有如下的伪代码：</p>\n<pre><code>IEnumerable&lt;Item&gt; items = GetItems();\nitems.ForEach(i =&gt; i.DoStuff());\n</code></pre><p>如何实现 <code>items.ForEach</code>的遍历呢？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><pre><code>public static void ForEach&lt;T&gt;(this IEnumerable&lt;T&gt; enumeration, Action&lt;T&gt; action)\n{\n    foreach(T item in enumeration)\n    {\n        action(item);\n    }\n}\n</code></pre><p>调用方法：</p>\n<pre><code>items.ToList().ForEach(i =&gt; i.DoStuff());\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><pre><code>public static class Enumerables\n{\n    public static void ForEach&lt;T&gt;(this IEnumerable&lt;T&gt; @this, Action&lt;T&gt; action)\n    {\n        foreach (T item in @this)\n        {\n            action(item);\n        }\n    }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>public static IEnumerable&lt;T&gt; ForEach&lt;T&gt;(this IEnumerable&lt;T&gt; enumeration, Action&lt;T&gt; action)\n{\n    foreach (T item in enumeration)\n    {\n        action(item);\n        yield return item;\n    }\n}\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0),
(30,'.NET[C#]LINQ中IEnumerable<T>如何实现动态LINQ排序？','<p>.NET[C#]LINQ中IEnumerable&lt;T&gt;如何实现动态LINQ排序？</p>\n<h2 id=\"h2-u65B9u6848u4E00\"><a name=\"方案一\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案一</h2><p>编写静态扩展方法，如下：</p>\n<pre><code>using Microsoft.CSharp.RuntimeBinder;\nusing System;\nusing System.Collections;\nusing System.Collections.Generic;\nusing System.Dynamic;\nusing System.Linq;\nusing System.Runtime.CompilerServices;\nstatic class Program\n{\n    private static class AccessorCache\n    {\n        private static readonly Hashtable accessors = new Hashtable();\n\n        private static readonly Hashtable callSites = new Hashtable();\n\n        private static CallSite&lt;Func&lt;CallSite, object, object&gt;&gt; GetCallSiteLocked(\n            string name) \n        {\n            var callSite = (CallSite&lt;Func&lt;CallSite, object, object&gt;&gt;)callSites[name];\n            if(callSite == null)\n            {\n                callSites[name] = callSite = CallSite&lt;Func&lt;CallSite, object, object&gt;&gt;\n                    .Create(Binder.GetMember(\n                                CSharpBinderFlags.None, \n                                name, \n                                typeof(AccessorCache),\n                                new CSharpArgumentInfo[] { \n                                    CSharpArgumentInfo.Create(\n                                        CSharpArgumentInfoFlags.None, \n                                        null) \n                                }));\n            }\n            return callSite;\n        }\n\n        internal static Func&lt;dynamic,object&gt; GetAccessor(string name)\n        {\n            Func&lt;dynamic, object&gt; accessor = (Func&lt;dynamic, object&gt;)accessors[name];\n            if (accessor == null)\n            {\n                lock (accessors )\n                {\n                    accessor = (Func&lt;dynamic, object&gt;)accessors[name];\n                    if (accessor == null)\n                    {\n                        if(name.IndexOf(&#39;.&#39;) &gt;= 0) {\n                            string[] props = name.Split(&#39;.&#39;);\n                            CallSite&lt;Func&lt;CallSite, object, object&gt;&gt;[] arr \n                                = Array.ConvertAll(props, GetCallSiteLocked);\n                            accessor = target =&gt;\n                            {\n                                object val = (object)target;\n                                for (int i = 0; i &lt; arr.Length; i++)\n                                {\n                                    var cs = arr[i];\n                                    val = cs.Target(cs, val);\n                                }\n                                return val;\n                            };\n                        } else {\n                            var callSite = GetCallSiteLocked(name);\n                            accessor = target =&gt;\n                            {\n                                return callSite.Target(callSite, (object)target);\n                            };\n                        }\n                        accessors[name] = accessor;\n                    }\n                }\n            }\n            return accessor;\n        }\n    }\n\n    public static IOrderedEnumerable&lt;dynamic&gt; OrderBy(\n        this IEnumerable&lt;dynamic&gt; source, \n        string property)\n    {\n        return Enumerable.OrderBy&lt;dynamic, object&gt;(\n            source, \n            AccessorCache.GetAccessor(property), \n            Comparer&lt;object&gt;.Default);\n    }\n\n    public static IOrderedEnumerable&lt;dynamic&gt; OrderByDescending(\n        this IEnumerable&lt;dynamic&gt; source, \n        string property)\n    {\n        return Enumerable.OrderByDescending&lt;dynamic, object&gt;(\n            source, \n            AccessorCache.GetAccessor(property), \n            Comparer&lt;object&gt;.Default);\n    }\n\n    public static IOrderedEnumerable&lt;dynamic&gt; ThenBy(\n        this IOrderedEnumerable&lt;dynamic&gt; source, \n        string property)\n    {\n        return Enumerable.ThenBy&lt;dynamic, object&gt;(\n            source, \n            AccessorCache.GetAccessor(property), \n            Comparer&lt;object&gt;.Default);\n    }\n\n    public static IOrderedEnumerable&lt;dynamic&gt; ThenByDescending(\n        this IOrderedEnumerable&lt;dynamic&gt; source, \n        string property)\n    {\n        return Enumerable.ThenByDescending&lt;dynamic, object&gt;(\n            source, \n            AccessorCache.GetAccessor(property), \n            Comparer&lt;object&gt;.Default);\n    }\n\n    static void Main()\n    {\n        dynamic a = new ExpandoObject(), \n                b = new ExpandoObject(), \n                c = new ExpandoObject();\n        a.X = &quot;abc&quot;;\n        b.X = &quot;ghi&quot;;\n        c.X = &quot;def&quot;;\n        dynamic[] data = new[] { \n            new { Y = a },\n            new { Y = b }, \n            new { Y = c } \n        };\n\n        var ordered = data.OrderByDescending(&quot;Y.X&quot;).ToArray();\n        foreach (var obj in ordered)\n        {\n            Console.WriteLine(obj.Y.X);\n        }\n    }\n}\n</code></pre><h2 id=\"h2-u65B9u6848u4E8C\"><a name=\"方案二\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案二</h2><p>使用动态 <em>LINQ</em>，首先添加引用：</p>\n<pre><code>using System.Linq.Dynamic;\n</code></pre><p>使用方法：</p>\n<pre><code>vehicles = vehicles.AsQueryable().OrderBy(&quot;Make ASC, Year DESC&quot;).ToList();\n</code></pre><h2 id=\"h2-u65B9u6848u4E09\"><a name=\"方案三\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案三</h2><pre><code>public static IEnumerable&lt;T&gt; OrderBy&lt;T&gt;(this IEnumerable&lt;T&gt; input, string queryString)\n{\n    if (string.IsNullOrEmpty(queryString))\n        return input;\n\n    int i = 0;\n    foreach (string propname in queryString.Split(&#39;,&#39;))\n    {\n        var subContent = propname.Split(&#39;|&#39;);\n        if (Convert.ToInt32(subContent[1].Trim()) == 0)\n        {\n            if (i == 0)\n                input = input.OrderBy(x =&gt; GetPropertyValue(x, subContent[0].Trim()));\n            else\n                input = ((IOrderedEnumerable&lt;T&gt;)input).ThenBy(x =&gt; GetPropertyValue(x, subContent[0].Trim()));\n        }\n        else\n        {\n            if (i == 0)\n                input = input.OrderByDescending(x =&gt; GetPropertyValue(x, subContent[0].Trim()));\n            else\n                input = ((IOrderedEnumerable&lt;T&gt;)input).ThenByDescending(x =&gt; GetPropertyValue(x, subContent[0].Trim()));\n        }\n        i++;\n    }\n\n    return input;\n}\n</code></pre><h2 id=\"h2-u65B9u6848u56DB\"><a name=\"方案四\" class=\"reference-link\"></a><span class=\"header-link octicon octicon-link\"></span>方案四</h2><pre><code>public static IEnumerable&lt;TEntity&gt; OrderBy&lt;TEntity&gt;(this IEnumerable&lt;TEntity&gt; source, \n                                                    string orderByProperty, bool desc)\n{\n    string command = desc ? &quot;OrderByDescending&quot; : &quot;OrderBy&quot;;\n    var type = typeof(TEntity);\n    var property = type.GetProperty(orderByProperty);\n    var parameter = Expression.Parameter(type, &quot;p&quot;);\n    var propertyAccess = Expression.MakeMemberAccess(parameter, property);\n    var orderByExpression = Expression.Lambda(propertyAccess, parameter);\n    var resultExpression = Expression.Call(typeof(Queryable), command, \n                                           new[] { type, property.PropertyType },\n                                           source.AsQueryable().Expression, \n                                           Expression.Quote(orderByExpression));\n    return source.AsQueryable().Provider.CreateQuery&lt;TEntity&gt;(resultExpression);\n}\n</code></pre>',0,'','2018-01-16 14:35:32','2018-01-16 14:35:32','\0','',0);

/*Table structure for table `tb_user` */

DROP TABLE IF EXISTS `tb_user`;

CREATE TABLE `tb_user` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `LoginName` varchar(50) NOT NULL DEFAULT '' COMMENT '登录名',
  `Password` varchar(150) NOT NULL DEFAULT '' COMMENT '密码',
  `DisplayName` varchar(50) DEFAULT '' COMMENT '显示名称',
  `RealName` varchar(30) DEFAULT '' COMMENT '真实姓名',
  `EmailAddress` varchar(120) DEFAULT '' COMMENT '电子邮箱',
  `Avatar` varchar(255) DEFAULT '' COMMENT '用户头像',
  `Status` int(2) NOT NULL DEFAULT '1' COMMENT '用户的状态,0:禁用,1:正常',
  `Telephone` varchar(20) DEFAULT '' COMMENT '手机号码',
  `Qq` varchar(15) DEFAULT '',
  `WebsiteUrl` varchar(200) DEFAULT '',
  `CreatedOn` datetime DEFAULT NULL COMMENT '用户创建时间',
  `CreatedIp` varchar(24) DEFAULT '' COMMENT '创建用户时的IP地址',
  `LoginCount` int(8) DEFAULT '0' COMMENT '登录次数累加器',
  `LatestLoginDate` datetime DEFAULT NULL COMMENT '最近一次登录时间',
  `LatestLoginIp` varchar(24) DEFAULT '' COMMENT '最近一次登录时的IP地址',
  `ModifiedOn` datetime DEFAULT NULL COMMENT '最近修改时间',
  `Type` int(2) DEFAULT '0' COMMENT '用户类型[-1:超级管理员,0:一般用户]',
  PRIMARY KEY (`Id`) USING BTREE,
  UNIQUE KEY `IX_LoginName` (`LoginName`) USING BTREE,
  UNIQUE KEY `IX_EmailAddress` (`EmailAddress`) USING BTREE,
  KEY `IX_CreatedOn` (`CreatedOn`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

/*Data for the table `tb_user` */

insert  into `tb_user`(`Id`,`LoginName`,`Password`,`DisplayName`,`RealName`,`EmailAddress`,`Avatar`,`Status`,`Telephone`,`Qq`,`WebsiteUrl`,`CreatedOn`,`CreatedIp`,`LoginCount`,`LatestLoginDate`,`LatestLoginIp`,`ModifiedOn`,`Type`) values 
(1,'admin','e10adc3949ba59abbe56e057f20f883e','',NULL,NULL,NULL,0,NULL,NULL,NULL,'0001-01-01 00:00:00',NULL,0,NULL,NULL,NULL,0);
